99%的人会踩MySQL这个坑!
The following article is from 码海 Author 坤哥
👇关注 51CTO技术栈,悦享技术,成就 CTO 梦想👇
今天跟大家聊一个 MySQL 的 Bug,这个 Bug 99% 的人会踩坑,不信咱们一起来看一看。
图片来自 Pexels
select * from order_info where uid = 5837661 order by id asc limit 1
CREATE TABLE `order_info` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`uid` int(11) unsigned,
`order_status` tinyint(3) DEFAULT NULL,
... 省略其它字段和索引
PRIMARY KEY (`id`),
KEY `idx_uid_stat` (`uid`,`order_status`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8
explain select * from order_info where uid = 5837661 order by id asc limit 1
我们知道 MySQL 是基于成本来选择是基于全表扫描还是选择某个索引来执行最终的执行计划的,所以看起来是全表扫描的成本小于基于 idx_uid_stat 索引执行的成本。
不过我的第一感觉很奇怪,这条 SQL 虽然是回表,但它的 limit 是 1,也就是说只选择了满足 uid = 5837661 中的其中一条语句,就算回表也只回一条记录,这种成本几乎可以忽略不计,优化器怎么会选择全表扫描呢。
为了查看 MySQL 优化器为啥选择了全表扫描,我打开了 optimizer_trace 来一探究竟。
画外音:在 MySQL 5.6 及之后的版本中,我们可以使用 optimizer trace 功能查看优化器生成执行计划的整个过程。
SET optimizer_trace="enabled=on"; // 打开 optimizer_trace
SELECT * FROM order_info where uid = 5837661 order by id asc limit 1
SELECT * FROM information_schema.OPTIMIZER_TRACE; // 查看执行计划表
SET optimizer_trace="enabled=off"; // 关闭 optimizer_trace
MySQL 优化器首先会计算出全表扫描的成本,然后选出该 SQL 可能涉及到的所有索引并且计算索引的成本,然后选出所有成本最小的那个来执行。
{
"rows_estimation": [
{
"table": "`rebate_order_info`",
"range_analysis": {
"table_scan": {
"rows": 21155996,
"cost": 4.45e6 // 全表扫描成本
}
},
...
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "idx_uid_stat",
"ranges": [
"5837661 <= uid <= 5837661"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 255918,
"cost": 307103, // 使用idx_uid_stat索引的成本
"chosen": true
}
],
"chosen_range_access_summary": { // 经过上面的各个成本比较后选择的最终结果
"range_access_plan": {
"type": "range_scan",
"index": "idx_uid_stat", // 可以看到最终选择了idx_uid_stat这个索引来执行
"rows": 255918,
"ranges": [
"58376617 <= uid <= 58376617"
]
},
"rows_for_plan": 255918,
"cost_for_plan": 307103,
"chosen": true
}
}
...
可以看到全表扫描的成本是 4.45e6,而选择索引 idx_uid_stat 的成本是 307103,远小于全表扫描的成本。
而且从最终的选择结果(chosen_range_access_summary)来看,确实也是选择了 idx_uid_stat 这个索引。
{
"reconsidering_access_paths_for_index_ordering": {
"clause": "ORDER BY",
"index_order_summary": {
"table": "`rebate_order_info`",
"index_provides_order": true,
"order_direction": "asc",
"index": "PRIMARY", // 可以看到选择了主键索引
"plan_changed": true,
"access_type": "index_scan"
}
}
}
这个选择表示由于排序的原因再进行了一次索引选择优化,由于我们的 SQL 使用了 id 排序(order by id asc limit 1),优化器最终选择了 PRIMARY 也就是全表扫描来执行。
The short explanation is that the optimizer thinks — or should I say hopes — that scanning the whole table (which is already sorted by the id field) will find the limited rows quick enough, and that this will avoid a sort operation. So by trying to avoid a sort, the optimizer ends-up losing time scanning the table.
从这段解释可以看出主要原因是由于我们使用了 order by id asc 这种基于 id 的排序写法,优化器认为排序是个昂贵的操作。
所以为了避免排序,并且它认为 limit n 的 n 如果很小的话即使使用全表扫描也能很快执行完。
所以它选择了全表扫描,也就避免了 id 的排序(全表扫描其实就是基于 id 主键的聚簇索引的扫描,本身就是基于 id 排好序的)。
如果这个选择是对的那也罢了,然而实际上这个优化却是有 bug 的!实际选择 idx_uid_stat 执行会快得多(只要 28 ms)!
网上有不少人反馈这个问题,而且出现这个问题基本只与 SQL 中出现 order by id asc limit n这种写法有关,如果 n 比较小很大概率会走全表扫描,如果 n 比较大则会选择正确的索引。
这个 bug 最早追溯到 2014 年,不少人都呼吁官方及时修正这个 bug,可能是实现比较困难,直到 MySQL 5.7,8.0 都还没解决。
所以在官方修复前我们要尽量避免这种写法,如果一定要用这种写法,怎么办呢,主要有两种方案。
select * from order_info force index(idx_uid_stat) where uid = 5837661 order by id asc limit 1
这种写法虽然可以,但不够优雅,如果这个索引被废弃了咋办?于是有了第二种比较优雅的方案。
select * from order_info where uid = 5837661 order by (id+0) asc limit 1
👇点击关注鸿蒙技术社区👇
了解鸿蒙一手资讯
作者:坤哥,前独角兽技术专家,现创业者,持续分享个人的成长收获
编辑:陶家龙
出处:转载自公众号码海(ID:seaofcode)
精彩文章推荐: